const express = require('express');
const router = express.Router();
const db = require('../db'); // 引入数据库连接池

// 获取所有书籍（支持搜索、分类、分页）
router.get('/books', async (req, res) => {
    const { query, category, page = 1, size = 10 } = req.query;

    try {
        let sql = 'SELECT * FROM books';
        let countSql = 'SELECT COUNT(*) as total FROM books';
        const params = [];

        if (query) {
            sql += ' WHERE name LIKE ? OR author LIKE ?';
            countSql += ' WHERE name LIKE ? OR author LIKE ?';
            params.push(`%${query}%`, `%${query}%`);
        }
        if (category) {
            if (params.length === 0) {
                sql += ' WHERE category = ?';
                countSql += ' WHERE category = ?';
            } else {
                sql += ' AND category = ?';
                countSql += ' AND category = ?';
            }
            params.push(category);
        }

        const [totalRows] = await db.execute(countSql, params);
        const total = totalRows[0].total;

        const offset = (page - 1) * size;
        sql += ' LIMIT ? OFFSET ?';
        params.push(parseInt(size), parseInt(offset));

        const [rows] = await db.execute(sql, params);

        res.json({
            books: rows,
            total: total,
        });
    } catch (error) {
        console.error('Error fetching books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 获取推荐书籍（随机选取）
router.get('/recommended-books', async (req, res) => {
    try {
        const sql = 'SELECT * FROM books ORDER BY RAND() LIMIT 5';
        const [rows] = await db.execute(sql);

        res.json({
            recommendedBooks: rows,
        });
    } catch (error) {
        console.error('Error fetching recommended books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 获取热门书籍（随机选取）
router.get('/popular-books', async (req, res) => {
    try {
        const sql = 'SELECT * FROM books ORDER BY RAND() LIMIT 5';
        const [rows] = await db.execute(sql);

        res.json({
            popularBooks: rows,
        });
    } catch (error) {
        console.error('Error fetching popular books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 获取用户评价
router.get('/reviews', async (req, res) => {
    try {
        const sql = 'SELECT * FROM reviews ORDER BY id DESC LIMIT 20';
        const [rows] = await db.execute(sql);

        res.json({
            userReviews: rows,
        });
    } catch (error) {
        console.error('Error fetching user reviews:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 获取卖书表数据（支持分页和搜索）
router.get('/sell-books', async (req, res) => {
    const { query, page = 1, size = 10 } = req.query;

    try {
        let sql = 'SELECT * FROM SellBooks';
        let countSql = 'SELECT COUNT(*) as total FROM SellBooks';
        const params = [];

        if (query) {
            sql += ' WHERE BookName LIKE ?';
            countSql += ' WHERE BookName LIKE ?';
            params.push(`%${query}%`);
        }

        const [totalRows] = await db.execute(countSql, params);
        const total = totalRows[0].total;

        const offset = (page - 1) * size;
        sql += ' LIMIT ? OFFSET ?';
        params.push(parseInt(size), parseInt(offset));

        const [rows] = await db.execute(sql, params);

        res.json({
            sellBooks: rows,
            total: total,
        });
    } catch (error) {
        console.error('Error fetching sell books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 上传书籍
router.post('/upload-book', async (req, res) => {
    const { BookName, description, price, STATUS } = req.body;

    if (!BookName || !price || !STATUS) {
        return res.status(400).json({ message: 'Missing required fields' });
    }

    if (!['可售', '已售出'].includes(STATUS)) {
        return res.status(400).json({ message: 'Invalid STATUS value' });
    }

    try {
        const insertSql = 'INSERT INTO SellBooks (BookName, description, price, STATUS) VALUES (?, ?, ?, ?)';
        const params = [BookName, description, price, STATUS];

        const [result] = await db.execute(insertSql, params);

        res.json({
            message: 'Book uploaded successfully',
            bookId: result.insertId,
        });
    } catch (error) {
        console.error('Error uploading book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 删除书籍（下架）
router.delete('/books/remove/:id', async (req, res) => {
    const { id } = req.params;

    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    try {
        const deleteSql = 'DELETE FROM SellBooks WHERE id = ?';
        const [result] = await db.execute(deleteSql, [id]);

        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        res.json({
            message: 'Book removed successfully',
        });
    } catch (error) {
        console.error('Error removing book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 更新书籍信息（更改价格或保存）
router.put('/books/update/:id', async (req, res) => {
    const { id } = req.params;
    const { BookName, description, price, STATUS } = req.body;

    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    if (!BookName || !price || !STATUS) {
        return res.status(400).json({ message: 'Missing required fields' });
    }

    if (!['可售', '已售出'].includes(STATUS)) {
        return res.status(400).json({ message: 'Invalid STATUS value' });
    }

    try {
        const updateSql = 'UPDATE SellBooks SET BookName = ?, description = ?, price = ?, STATUS = ? WHERE id = ?';
        const params = [BookName, description, price, STATUS, id];

        const [result] = await db.execute(updateSql, params);

        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        res.json({
            message: 'Book updated successfully',
        });
    } catch (error) {
        console.error('Error updating book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 获取出租书籍（支持搜索、分类、分页）
router.get('/rented-books', async (req, res) => {
    const { query, category, page = 1, size = 10 } = req.query;

    try {
        let sql = 'SELECT * FROM RentedBooks';
        let countSql = 'SELECT COUNT(*) as total FROM RentedBooks';
        const params = [];

        // 搜索条件
        if (query) {
            sql += ' WHERE name LIKE ? OR author LIKE ?';
            countSql += ' WHERE name LIKE ? OR author LIKE ?';
            params.push(`%${query}%`, `%${query}%`);
        }

        // 分类条件
        if (category) {
            if (params.length === 0) {
                sql += ' WHERE category = ?';
                countSql += ' WHERE category = ?';
            } else {
                sql += ' AND category = ?';
                countSql += ' AND category = ?';
            }
            params.push(category);
        }

        // 获取总记录数
        const [totalRows] = await db.execute(countSql, params);
        const total = totalRows[0].total;

        // 分页
        const offset = (page - 1) * size;
        sql += ' LIMIT ? OFFSET ?';
        params.push(parseInt(size), parseInt(offset));

        // 执行查询
        const [rows] = await db.execute(sql, params);

        res.json({
            rentedBooks: rows,
            total: total,
        });
    } catch (error) {
        console.error('Error fetching rented books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 获取租入书籍（支持搜索、分类、分页）
router.get('/borrowed-books', async (req, res) => {
    const { query, category, page = 1, size = 10 } = req.query;

    try {
        let sql = 'SELECT * FROM BorrowedBooks';
        let countSql = 'SELECT COUNT(*) as total FROM BorrowedBooks';
        const params = [];

        // 搜索条件
        if (query) {
            sql += ' WHERE name LIKE ? OR author LIKE ?';
            countSql += ' WHERE name LIKE ? OR author LIKE ?';
            params.push(`%${query}%`, `%${query}%`);
        }

        // 分类条件
        if (category) {
            if (params.length === 0) {
                sql += ' WHERE category = ?';
                countSql += ' WHERE category = ?';
            } else {
                sql += ' AND category = ?';
                countSql += ' AND category = ?';
            }
            params.push(category);
        }

        // 获取总记录数
        const [totalRows] = await db.execute(countSql, params);
        const total = totalRows[0].total;

        // 分页
        const offset = (page - 1) * size;
        sql += ' LIMIT ? OFFSET ?';
        params.push(parseInt(size), parseInt(offset));

        // 执行查询
        const [rows] = await db.execute(sql, params);

        res.json({
            borrowedBooks: rows,
            total: total,
        });
    } catch (error) {
        console.error('Error fetching borrowed books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 提交出租书籍
router.post('/rented-books', async (req, res) => {
    const { name, author, price, category, intro, rent_date, return_date } = req.body;

    // 校验必填字段
    if (!name || !author || !price || !category || !rent_date) {
        return res.status(400).json({ message: 'Missing required fields' });
    }

    // 校验价格字段
    if (typeof price !== 'number' || price < 0) {
        return res.status(400).json({ message: 'Invalid price value' });
    }

    // 校验 rent_date 字段
    if (!/^\d{4}-\d{2}-\d{2}$/.test(rent_date)) {
        return res.status(400).json({ message: 'Invalid rent_date format' });
    }

    try {
        // 插入出租书籍数据到数据库
        const insertSql = `
            INSERT INTO RentedBooks (name, author, price, category, intro, rent_date, return_date)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        `;
        const params = [name, author, price, category, intro, rent_date, return_date || null];

        const [result] = await db.execute(insertSql, params);

        // 返回成功信息
        res.status(201).json({
            message: 'Rented book added successfully',
            bookId: result.insertId,
        });
    } catch (error) {
        console.error('Error adding rented book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 删除出租书籍
router.delete('/rented-books/:id', async (req, res) => {
    const { id } = req.params;

    // 校验书籍 ID
    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    try {
        // 删除数据库中的出租书籍记录
        const deleteSql = 'DELETE FROM RentedBooks WHERE id = ?';
        const [result] = await db.execute(deleteSql, [id]);

        // 检查是否删除成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            message: 'Rented book deleted successfully',
        });
    } catch (error) {
        console.error('Error deleting rented book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 更新出租书籍
router.put('/rented-books/:id', async (req, res) => {
    const { id } = req.params;
    const { name, author, price, category, intro, rent_date, return_date } = req.body;

    // 校验必填字段
    if (!id || !name || !author || !price || !category || !rent_date) {
        return res.status(400).json({ message: 'Missing required fields' });
    }

    // 校验价格字段
    if (typeof price !== 'number' || price < 0) {
        return res.status(400).json({ message: 'Invalid price value' });
    }

    // 校验 rent_date 字段
    if (!/^\d{4}-\d{2}-\d{2}$/.test(rent_date)) {
        return res.status(400).json({ message: 'Invalid rent_date format' });
    }

    try {
        // 更新数据库中的出租书籍记录
        const updateSql = `
            UPDATE RentedBooks
            SET name = ?, author = ?, price = ?, category = ?, intro = ?, rent_date = ?, return_date = ?
            WHERE id = ?
        `;
        const params = [name, author, price, category, intro, rent_date, return_date || null, id];

        const [result] = await db.execute(updateSql, params);

        // 检查是否更新成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            message: 'Rented book updated successfully',
        });
    } catch (error) {
        console.error('Error updating rented book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});


// 获取租入书籍（支持搜索、分类、分页）
router.get('/borrowed-books', async (req, res) => {
    const { query, category, page = 1, size = 10 } = req.query;

    try {
        let sql = 'SELECT * FROM BorrowedBooks';
        let countSql = 'SELECT COUNT(*) as total FROM BorrowedBooks';
        const params = [];

        // 搜索条件
        if (query) {
            sql += ' WHERE name LIKE ? OR author LIKE ?';
            countSql += ' WHERE name LIKE ? OR author LIKE ?';
            params.push(`%${query}%`, `%${query}%`);
        }

        // 分类条件
        if (category) {
            if (params.length === 0) {
                sql += ' WHERE category = ?';
                countSql += ' WHERE category = ?';
            } else {
                sql += ' AND category = ?';
                countSql += ' AND category = ?';
            }
            params.push(category);
        }

        // 获取总记录数
        const [totalRows] = await db.execute(countSql, params);
        const total = totalRows[0].total;

        // 分页
        const offset = (page - 1) * size;
        sql += ' LIMIT ? OFFSET ?';
        params.push(parseInt(size), parseInt(offset));

        // 执行查询
        const [rows] = await db.execute(sql, params);

        res.json({
            borrowedBooks: rows,
            total: total,
        });
    } catch (error) {
        console.error('Error fetching borrowed books:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 添加租入书籍
router.post('/borrowed-books', async (req, res) => {
    const { name, author, price, category, intro, rentalTime, returnTime, status } = req.body;

    // 校验必填字段
    if (!name || !author || !price || !category || !rentalTime || !status) {
        return res.status(400).json({ message: 'Missing required fields' });
    }

    // 校验状态字段
    if (!['未归还', '已归还'].includes(status)) {
        return res.status(400).json({ message: 'Invalid status value' });
    }

    // 校验价格字段
    if (typeof price !== 'number' || price < 0) {
        return res.status(400).json({ message: 'Invalid price value' });
    }

    // 校验 rentalTime 字段
    if (!/^\d{4}-\d{2}-\d{2}$/.test(rentalTime)) {
        return res.status(400).json({ message: 'Invalid rentalTime format' });
    }

    try {
        // 插入租入书籍数据到数据库
        const insertSql = `
            INSERT INTO BorrowedBooks (name, author, price, category, intro, rentalTime, returnTime, status)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `;
        const params = [name, author, price, category, intro, rentalTime, returnTime || null, status];

        const [result] = await db.execute(insertSql, params);

        // 返回成功信息
        res.status(201).json({
            message: 'Borrowed book added successfully',
            bookId: result.insertId,
        });
    } catch (error) {
        console.error('Error adding borrowed book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 更新租入书籍
router.put('/borrowed-books/:id', async (req, res) => {
    const { id } = req.params;
    const { name, author, price, category, intro, rentalTime, returnTime, status } = req.body;

    // 校验必填字段
    if (!id || !name || !author || !price || !category || !rentalTime || !status) {
        return res.status(400).json({ message: 'Missing required fields' });
    }

    // 校验状态字段
    if (!['未归还', '已归还'].includes(status)) {
        return res.status(400).json({ message: 'Invalid status value' });
    }

    // 校验价格字段
    if (typeof price !== 'number' || price < 0) {
        return res.status(400).json({ message: 'Invalid price value' });
    }

    // 校验 rentalTime 字段
    if (!/^\d{4}-\d{2}-\d{2}$/.test(rentalTime)) {
        return res.status(400).json({ message: 'Invalid rentalTime format' });
    }

    try {
        // 更新数据库中的租入书籍记录
        const updateSql = `
            UPDATE BorrowedBooks
            SET name = ?, author = ?, price = ?, category = ?, intro = ?, rentalTime = ?, returnTime = ?, status = ?
            WHERE id = ?
        `;
        const params = [name, author, price, category, intro, rentalTime, returnTime || null, status, id];

        const [result] = await db.execute(updateSql, params);

        // 检查是否更新成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            message: 'Borrowed book updated successfully',
        });
    } catch (error) {
        console.error('Error updating borrowed book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 删除租入书籍
router.delete('/borrowed-books/:id', async (req, res) => {
    const { id } = req.params;

    // 校验书籍 ID
    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    try {
        // 删除数据库中的租入书籍记录
        const deleteSql = 'DELETE FROM BorrowedBooks WHERE id = ?';
        const [result] = await db.execute(deleteSql, [id]);

        // 检查是否删除成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            message: 'Borrowed book deleted successfully',
        });
    } catch (error) {
        console.error('Error deleting borrowed book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 续租书籍
router.post('/renew-book/:id', async (req, res) => {
    const { id } = req.params;
    const { rental_time } = req.body;

    // 校验书籍 ID
    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    // 校验 rental_time 字段
    if (!rental_time) {
        return res.status(400).json({ message: 'Missing rental_time' });
    }

    try {
        // 更新数据库中的租入时间
        const updateSql = `
            UPDATE BorrowedBooks
            SET rental_time = ?
            WHERE id = ?
        `;
        const [result] = await db.execute(updateSql, [rental_time, id]);

        // 检查是否更新成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            success: true,
            rental_time: rental_time,
        });
    } catch (error) {
        console.error('Error renewing book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});


// 归还书籍
router.post('/return-book/:id', async (req, res) => {
    const { id } = req.params;
    const { return_time } = req.body;

    // 校验书籍 ID
    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    // 校验 return_time 字段
    if (!return_time) {
        return res.status(400).json({ message: 'Missing return_time' });
    }

    try {
        // 更新数据库中的归还时间和状态
        const updateSql = `
            UPDATE BorrowedBooks
            SET return_time = ?, status = '已归还'
            WHERE id = ?
        `;
        const [result] = await db.execute(updateSql, [return_time, id]);

        // 检查是否更新成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            success: true,
            return_time: return_time,
        });
    } catch (error) {
        console.error('Error returning book:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 标记书籍为已归还
router.post('/mark-returned/:id', async (req, res) => {
    const { id } = req.params;

    // 校验书籍 ID
    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    try {
        // 更新数据库中的状态为已归还
        const updateSql = `
            UPDATE BorrowedBooks
            SET status = '已归还'
            WHERE id = ?
        `;
        const [result] = await db.execute(updateSql, [id]);

        // 检查是否更新成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            success: true,
        });
    } catch (error) {
        console.error('Error marking book as returned:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});

// 标记书籍为未归还
router.post('/mark-unreturned/:id', async (req, res) => {
    const { id } = req.params;

    // 校验书籍 ID
    if (!id) {
        return res.status(400).json({ message: 'Missing book ID' });
    }

    try {
        // 更新数据库中的状态为未归还
        const updateSql = `
            UPDATE BorrowedBooks
            SET status = '未归还'
            WHERE id = ?
        `;
        const [result] = await db.execute(updateSql, [id]);

        // 检查是否更新成功
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: 'Book not found' });
        }

        // 返回成功信息
        res.status(200).json({
            success: true,
        });
    } catch (error) {
        console.error('Error marking book as unreturned:', error);
        res.status(500).json({ message: 'Internal Server Error' });
    }
});


module.exports = router;